package kt.dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
import java.util.NoSuchElementException;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

public class DBPoolX {
	private static Logger logger = LogManager.getLogger(DBPoolX.class);
	public static final int MAX_CONNECTIONS = 5;
	private static Map<String, LinkedList<Connection>> poolTable = new Hashtable<String, LinkedList<Connection>>();
	private static Map<String, DBPoolXData> dataTable = new Hashtable<String, DBPoolXData>();
	private static Map<String, DBPoolX> dbpoolTable = new Hashtable<String, DBPoolX>();

	private LinkedList<Connection> pool = null;
	private DBPoolXData data = null;
	private String name = null;

	private Connection conn = null;

	static {
		if (!DBPoolXData.isLoaded())
			DBPoolXData.loadData();

		buildPools();
	}

	public String getName() {
		return this.name;
	}

	private DBPoolX(String name, LinkedList<Connection> pool, DBPoolXData data) {
		this.name = name;
		this.pool = pool;
		this.data = data;
	}

	public static DBPoolX getInstance(String poolName)
			throws DBPoolNotFoundException {
		DBPoolX poolX = (DBPoolX) dbpoolTable.get(poolName);
		if (poolX == null)
			throw new DBPoolNotFoundException("Pool not found: " + poolName);

		return poolX;
	}

	private static void buildPools() {
		DBPoolXData data = null;
		for (int i = 0; i < DBPoolXData.getPoolDataList().size(); i++) {
			data = (DBPoolXData) DBPoolXData.getPoolDataList().get(i);

			if (data == null)
				continue;
			logger.info("build pools for data "  + data);
			buildPool(data);
		}
	}

	private static void buildPool(DBPoolXData data) {
		try {
			LinkedList<Connection> pool = new LinkedList<Connection>();
			Connection conn = null;
			for (int j = 0; j < data.getParameter().getCount(); j++) {
				System.out.println("+++++ Make DB connection +++++++");
				conn = makeDBConnection(data.getParameter());
				if (conn == null)
					continue;
				pool.addLast(conn);
			}

			String poolName = null;
			for (int j = 0; j < data.getPoolnames().size(); j++) {
				poolName = data.getPoolnames().get(j);
				logger.info("add to POOL table: poolName = " + poolName + "; poolSize="+pool.size());
				poolTable.put(poolName, pool);
				logger.info("add to data table: poolName = " + poolName + "; data="+data);
				dataTable.put(poolName, data);
				dbpoolTable.put(poolName, new DBPoolX(poolName, pool, data));
			}
		} catch (SQLException ex) {
			logger.error("Error: buildPool", ex);
			System.out.println("Khong noi dc voi database roi !");
		}
	}

	public static void releaseAll() {
		System.out.println("Closing connections in ALL pools... ");
		logger.info("Closing connections in ALL pools... ");
		String poolName = null;
		DBPoolX dbPoolX = null;
		for (Iterator<String> it = dbpoolTable.keySet().iterator(); it.hasNext();) {
			poolName = (String) it.next();
			dbPoolX = (DBPoolX) dbpoolTable.get(poolName);
			dbPoolX.release();
		}
	}

	public synchronized Connection getConnection() {
		this.conn = null;
		while (this.conn == null) {
			logger.debug("Get the connection from the pool");
			try {
				synchronized (this.pool) {
					try { 
						if (this.pool.size() > 0) {		
							logger.info("get connect from pool " + this.name);
							this.conn = ((Connection) this.pool.removeFirst());
						}
					} catch (NoSuchElementException ex) {
						logger.error("error when getConnection from the pool", ex);
					}
				}
				if (this.conn == null) {
					logger.info("the connection is null, make new connection");
					this.conn = makeDBConnection(this.data.getParameter());
				} else if (this.conn.isClosed()) {
					logger.info("the connection is closed, make new connection");
					this.conn = makeDBConnection(this.data.getParameter());
				}  //TODO need to check isValid or not?	
				if(this.conn.isClosed()) {
					logger.info("The connection is closed, set it to null to get new connection");
					this.conn = null;
				}
			} catch (SQLException sqlX){
				this.conn = null;
				logger.error("get SQL exception when try to get the connection: ", sqlX);
				tryToSleep(500L);
			} catch (Exception ex) {
				logger.error("getConnection throw exception: " + ex.getMessage());
				tryToSleep(500L);
			} 
//			finally {
//				try {
//					if (this.conn != null && !this.conn.isClosed())
//						this.conn.setAutoCommit(true);
//					else
//						Thread.sleep(500L);
//				} catch (Exception localException2) {
//				}
//			}
		}
		return this.conn;
	}
	
	private void tryToSleep(long miliSecond){
		try {
			Thread.sleep(miliSecond);
		} catch (Exception localException1) {
		}
	}

	public void putConnection(Connection conn) {
		try {
			if ((conn == null) || (conn.isClosed())) {
				System.out.println("putConnection: conn is null or closed: " + conn);
				return;
			}

			synchronized (this.pool) {
				if (this.pool.size() >= 5) {
					conn.close();
					return;
				}
				this.pool.addLast(conn);
				this.pool.notify();
			}
		} catch (SQLException localSQLException) {
			logger.error("error when putConnection!", localSQLException);
		}
	}

	public void release() {
		System.out.println("Closing connections in pool " + getName());
		logger.info("Closing connections in pool " + getName());
		synchronized (this.pool) {
			for (int i = 0; i < this.pool.size(); i++) {
				this.conn = ((Connection) this.pool.removeFirst());
				if (this.conn == null)
					continue;
				try {
					this.conn.close();
				} catch (Exception e) {
					System.out.println("release: Cannot close connection! (maybe closed?)");
					logger.error("release: Cannot close connection! (maybe closed?)", e);
				}
			}
			this.pool.clear();
		}
	}

	public int size() {
		synchronized (this.pool) {
			return this.pool.size();
		}
	}

	public boolean isEmpty() {
		synchronized (this.pool) {
			return this.pool.isEmpty();
		}
	}

	public void finalize() {
		System.out.println("Pool " + getName() + " called finalize()");
		logger.info("Pool " + getName() + " called finalize()");
		release();
	}

	private static Connection makeDBConnection(ConnectionParameter param)
			throws SQLException {
		Connection conn = null;
		try {
			Class.forName(param.getDriver());
			
			logger.info("makeDBConnection Driver " + param.getDriver());
			logger.info("makeDBConnection "+ param.getUrl() + ", " + param.getUser() + ", " + param.getPassword());
			
			conn = DriverManager.getConnection(param.getUrl(), param.getUser(), param.getPassword());
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
			logger.error("error when makeDBConnection", ex);
		}
		return conn;
	}

	public static void load() {
	}

	public void releaseConnection(Connection conn, PreparedStatement preStmt) {
		try {
			if (preStmt != null)
				preStmt.close();

			if ((conn == null) || (conn.isClosed()))
				return;

			synchronized (this.pool) {
				if (this.pool.size() >= 5) {
					conn.close();
					return;
				}
				this.pool.addLast(conn);
				this.pool.notify();
			}
		} catch (SQLException localSQLException) {
			logger.error("error when release releaseConnection", localSQLException);
		}
	}

	public void releaseConnection(Connection conn, PreparedStatement preStmt,
			ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException localSQLException) {
			logger.error("error when release releaseConnection", localSQLException);
		}
		releaseConnection(conn, preStmt);
	}

	public void releaseConnection(Connection conn, PreparedStatement preStmt,
			Statement stmt, ResultSet rs) {
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException localSQLException) {
			logger.error("error when release releaseConnection", localSQLException);
		}
		releaseConnection(conn, preStmt, rs);
	}
}
